package library.server;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class QueryMaker {
	// QUERIES
	private static final String InsertBookQuery = "INSERT INTO Libro(ISDN, Titolo, Editore, Prezzo, Data) VALUES(?,?,?,?,?)";
	private static final String InsertBookAuthorsQuery = "INSERT INTO Libro_has_Autore(Libro_ISDN, Autore_Nome, Autore_Cognome) VALUES(?,?,?)";
	private static final String DeleteBookQuery = "DELETE FROM Libro WHERE ISDN=?";
	private static final String SelectAllBooksQuery = "SELECT * FROM Libro";
	private static final String SelectBookAuthorsQuery = "SELECT * FROM Libro_has_Autore WHERE Libro_ISDN=?";
	// END QUERIES
	
	public static boolean insertBook(MySQLConnection connection, Book b) {
		boolean result = false;
		PreparedStatement statement = null;
		try {
			statement = connection.createPreparedStatement(InsertBookQuery);
			statement.setString(1, b.getISDN());
			statement.setString(2, b.getTitle());
			statement.setString(3, b.getPublisher());
			statement.setFloat(4, b.getPrice());
			statement.setDate(5, new Date(b.getPublicationDate().getTime()));
			statement.executeUpdate();
			statement.close();
			
			statement = connection
					.createPreparedStatement(InsertBookAuthorsQuery);
			for (Author a : b.getAuthors()) {
				statement.setString(1, b.getISDN());
				statement.setString(2, a.getName());
				statement.setString(3, a.getSurname());
				statement.executeUpdate();
			}
			statement.close();
			result = true;
		} catch (SQLException e) {
			result = false;
		} 

		return result;
	}

	public static boolean deleteBook(MySQLConnection connection, String isdn) {
		boolean result = false;
		try {
			PreparedStatement statement = connection
					.createPreparedStatement(DeleteBookQuery);
			statement.setString(1, isdn);
			result = statement.executeUpdate() > 0;
			statement.close();
			
		} catch (SQLException e) {
			result = false;
		}

		return result;
	}

	public static List<Book> getAllBooks(MySQLConnection connection) {
		
		List<Book> books = new ArrayList<Book>();
		try {
			ResultSet rs = connection.executeQuery(SelectAllBooksQuery);
			PreparedStatement authorsStatement = connection
					.createPreparedStatement(SelectBookAuthorsQuery);
			
			while (rs.next()) {
				String isdn = rs.getString("ISDN");
				String title = rs.getString("Titolo");
				String publisher = rs.getString("Editore");
				Date date = rs.getDate("Data");
				Float price = rs.getFloat("Prezzo");
				
				authorsStatement.setString(1, isdn);
				ResultSet authorResultSet = authorsStatement.executeQuery();
				List<Author> authors = new ArrayList<Author>();
				while (authorResultSet.next()) {
					authors.add(new Author(authorResultSet
							.getString("Autore_Nome"), authorResultSet
							.getString("Autore_Cognome")));
				}
				authorResultSet.close();
				
				books.add(new Book(isdn, title, publisher, authors, price, date));
			}

			authorsStatement.close();
			rs.close();

		} catch (SQLException e) {
			books = null;
		}

		return books;
	}
}
